package com.py.credit.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.py.credit.common.Page;
import com.py.credit.common.ServiceException;
import com.py.credit.dao.IntegratedQueryDao;
import com.py.credit.dao.OfficeDao;
import com.py.credit.entity.*;
import com.py.credit.service.IntegratedQueryService;
import com.py.credit.utils.SQLBuilder;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class IntegratedQueryServiceImpl implements IntegratedQueryService {

    @Autowired
    private IntegratedQueryDao integratedQueryDao;

    @Autowired
    private OfficeDao officeDao;

    @Override
    public List<Map<String, Object>> selectByConditionForPage(String status,String lingyu,String jclb, String type, List<String> showColumns, String[] additionalColumns, String condition, Page page) {
        if (CollectionUtils.isEmpty(showColumns)) {
            throw new ServiceException("至少选择一列");
        }
        List<String> conditions = SQLBuilder.createConditionSql(condition);

        return integratedQueryDao.selectByConditionForPage(status,lingyu,jclb, type, showColumns, additionalColumns, conditions, page);
    }

    @Override
    public int selectFkDept(String ajbh) {
        return integratedQueryDao.selectFkDept(ajbh);
    }

    @Override
    public IntegratedQuery selectByAjbh(String ajbh) {
        return integratedQueryDao.selectByAjbh(ajbh);
    }

    @Override
    public List<Measure> selectCsByAjbhForPage(String type, int gzId, Page page) {
        return integratedQueryDao.selectCsByAjbhForPage(type, gzId, page);
    }

    @Override
    public Companymess selectByTybm(String tybm, String type) {
        if (type.equals("C")) {
            return integratedQueryDao.selectQYXXByTybm(tybm);
        } else {
            return integratedQueryDao.selectGRXXByTybm(tybm);
        }
    }

    @Override
    public Person selectZrrInfo(String idcard) {
        return integratedQueryDao.selectZrrInfo(idcard);
    }

    @Override
    public List<Feedback> feedBackListForPage(String ajbh, String managerId, Page page) {
        List<Feedback> feedbacks = integratedQueryDao.feedBackListForPage(ajbh, managerId, page);

        for (Feedback feedback:feedbacks
                ) {
            feedback.setMeasureList(integratedQueryDao.selectFeedbackCsList(feedback.getId()));
        }

        return feedbacks;
    }

    @Override
    public List<DeptNum> selectNumForDept(String ajbh) {
        return integratedQueryDao.selectNumForDept(ajbh);
    }

    @Override
    public List<Integer> selectDeptId(int gzId) {
        return integratedQueryDao.selectDeptId(gzId);
    }

    @Override
    public int selectAllDept() {
        return integratedQueryDao.selectAllDept();
    }

    @Override
    public List<Measure> selectFkCsByAjbh(String type, int gzId, String deptId) {
        // TODO Auto-generated method stub
        return integratedQueryDao.selectFkCsByAjbh(type, gzId, deptId);
    }

    @Override
    public List<Map<String, String>> selectFeedBackInfo(String ajbh) {
        return integratedQueryDao.selectFeedBackInfo(ajbh);
    }

    @Override
    public int selectCyDeptIsAllDept(int gzId) {
        return integratedQueryDao.selectCyDeptIsAllDept(gzId);
    }

    @Override
    public String selectCyDeptName(int gzId) {
        return integratedQueryDao.selectCyDeptName(gzId);
    }

    @Override
    public int selectExistAllDeptByAjbh(String ajbh) {
        return integratedQueryDao.selectExistAllDeptByAjbh(ajbh);
    }

    @Override
    public int selectNumForAllDept(String ajbh) {
        return integratedQueryDao.selectNumForAllDept(ajbh);
    }

    @Override
    public void excelExport(HttpServletResponse response, String deptId, String jclb) {
        queryTableExecle(response, deptId, jclb);
    }

    @Override
    public void exportFkzbExcel(HttpServletResponse response, String ajbh) {
        queryFkzbTableExecle(response, ajbh);
    }

    @Override
    public List<IntegratedQuery> getChartsDetailListForPage(String jclb, String ztmc, String startTime, String endTime, Page page) {
        return integratedQueryDao.getChartsDetailListForPage(jclb, ztmc, startTime, endTime, page);
    }

    @Override
    public List<LinkedHashMap> selectFkzbListForPage(String ajbh,Page page) {
        @SuppressWarnings("rawtypes")
		List<LinkedHashMap> mapList = integratedQueryDao.selectFkzbListForPage(ajbh,page);
        
        for (LinkedHashMap map:mapList) {
        	
        	String csId = (String)map.get("csId");
        	List<Long> rest = officeDao.selectTopAndDownwardId((Long) map.get("deptId"));
        	
            if(((Long)map.get("deptId")).equals(-1L)){
                int fkcs = integratedQueryDao.selectFkcs(ajbh, csId, null);
                map.put("fkzt",fkcs==0?"N":"Y");
                map.put("fkcs",fkcs);
            }else{
                int fkcs = integratedQueryDao.selectFkcs(ajbh, csId,rest);
                map.put("fkzt",fkcs==0?"N":"Y");
                map.put("fkcs",fkcs);
            }
        }
        
        return mapList;
    }

    private void queryTableExecle(HttpServletResponse response, String deptId, String jclb) {
        // 生成提示信息
        response.setContentType("application/vnd.ms-excel");
        HSSFWorkbook workbook = null;
        OutputStream fOut = null;
        String codedFileName = "本部门联合惩戒事件";

        // 进行转码，使其支持中文文件名
        try {
            codedFileName = URLEncoder.encode(codedFileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");

        // 产生工作簿对象
        workbook = new HSSFWorkbook();
        // 产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "本部门法人联合惩戒事件");
        HSSFRow row = null;
        HSSFCell cell = null;

        String[] strBT = {"事件编号", "机构名称", "统一社会信用编码", "发起部门", "发起时间", "联合惩戒对象", "数据状态", "反馈占比", "惩戒截止时间"};
        List<IntegratedQuery> exportList = integratedQueryDao.selectExportIntegratedQueryList(deptId, "C", jclb);

        //创建标题行
        row = sheet.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < exportList.size(); i++) {
            row = sheet.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getBm());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getDeptname());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getSxsj());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getGzmc());

            cell = row.createCell(6);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (exportList.get(i).getStatus().equals("J")) {
                cell.setCellValue("已发起");
            } else if (exportList.get(i).getStatus().equals("G")) {
                cell.setCellValue("超期移出");
            } else {
                cell.setCellValue("名单移出");
            }

            cell = row.createCell(7);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            List<Integer> list = integratedQueryDao.selectDeptId(exportList.get(i).getGzId());
            int ygNum = 0;
            if (list.contains(-1L)) {
                ygNum = integratedQueryDao.selectAllDept();
            } else {
                ygNum = list.size();
            }

            int sjNum = integratedQueryDao.selectFkDept(exportList.get(i).getAjbh());
            String num = sjNum + "/" + ygNum;
            cell.setCellValue(num);

            cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getRepairTime());
        }

        HSSFSheet sheet2 = workbook.createSheet();
        workbook.setSheetName(1, "本部门自然人联合惩戒事件");
        row = null;
        cell = null;
        exportList = integratedQueryDao.selectExportIntegratedQueryList(deptId, "P", jclb);

        //创建标题行
        String[] zrrStrBT = {"事件编号", "姓名", "证件号码", "发起部门", "发起时间", "联合惩戒对象", "数据状态", "反馈占比", "惩戒截止时间"};
        row = sheet2.createRow(0);
        for (int i = 0; i < zrrStrBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrStrBT[i]);
        }

        for (int i = 0; i < exportList.size(); i++) {
            row = sheet2.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getBm());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getDeptname());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getSxsj());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getGzmc());

            cell = row.createCell(6);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (exportList.get(i).getStatus().equals("J")) {
                cell.setCellValue("已发起");
            } else if (exportList.get(i).getStatus().equals("G")) {
                cell.setCellValue("超期移出");
            } else {
                cell.setCellValue("名单移出");
            }

            cell = row.createCell(7);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            List<Integer> list = integratedQueryDao.selectDeptId(exportList.get(i).getGzId());
            int ygNum = 0;
            if (list.contains(-1L)) {
                ygNum = integratedQueryDao.selectAllDept();
            } else {
                ygNum = list.size();
            }

            int sjNum = integratedQueryDao.selectFkDept(exportList.get(i).getAjbh());
            String num = sjNum + "/" + ygNum;
            cell.setCellValue(num);

            cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(exportList.get(i).getRepairTime());
        }

        try {
            fOut = response.getOutputStream();
            workbook.write(fOut);
            workbook.close();
            fOut.flush();
            fOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void queryFkzbTableExecle(HttpServletResponse response,String ajbh) {
        // 生成提示信息
        response.setContentType("application/vnd.ms-excel");
        HSSFWorkbook workbook = null;
        OutputStream fOut = null;
        String codedFileName = "联合奖惩事件反馈占比";

        // 进行转码，使其支持中文文件名
        try {
            codedFileName = URLEncoder.encode(codedFileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");

        // 产生工作簿对象
        workbook = new HSSFWorkbook();
        // 产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "反馈占比详情");
        HSSFRow row = null;
        HSSFCell cell = null;

        String[] strBT = {"事件编号", "主体名称", "编码", "规则名称", "措施名称", "反馈部门", "反馈状态", "反馈次数"};
        List<LinkedHashMap> data = integratedQueryDao.selectFkzbExcelList(ajbh);

        //创建标题行
        row = sheet.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String) data.get(i).get("ajbh"));

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String) data.get(i).get("ztmc"));

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String) data.get(i).get("bm"));

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String) data.get(i).get("gzmc"));

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String) data.get(i).get("csmc"));

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String) data.get(i).get("fkbm"));

            if(((Long)(data.get(i).get("deptId"))).equals(-1L)){
                int fkcs = integratedQueryDao.selectFkcs(ajbh, (String) data.get(i).get("csId"), null);

                cell = row.createCell(6);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(fkcs==0?"未反馈":"已反馈");

                cell = row.createCell(7);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(fkcs);
            }else{
                int fkcs = integratedQueryDao.selectFkcs(ajbh, (String) data.get(i).get("csId"),officeDao.selectTopAndDownwardId((Long) data.get(i).get("deptId")));

                cell = row.createCell(6);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(fkcs==0?"未反馈":"已反馈");

                cell = row.createCell(7);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(fkcs);
            }
        }

        try {
            fOut = response.getOutputStream();
            workbook.write(fOut);
            workbook.close();
            fOut.flush();
            fOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public List<Map> getLhcj(String ztmc,String bm,String type){
    	return integratedQueryDao.getLhcj(ztmc,bm,type);
    }
}
